package com.datareport.common.utils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 统计结果表工具类
 * 用于生成动态表名和构建SQL语句
 */
public class ResultTableUtils {
    
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMdd");
    private static final DateTimeFormatter MONTH_FORMATTER = DateTimeFormatter.ofPattern("yyyyMM");
    
    /**
     * 生成统计结果表名
     * 格式：stat_result_{ruleId}_{yyyyMMdd}
     * @param ruleId 规则ID
     * @param date 日期
     * @return 表名
     */
    public static String generateTableName(String ruleId, LocalDateTime date) {
        String dateStr = date.format(DATE_FORMATTER);
        return "stat_result_" + ruleId + "_" + dateStr;
    }
    
    /**
     * 生成统计结果表名（当前日期）
     * @param ruleId 规则ID
     * @return 表名
     */
    public static String generateTableName(String ruleId) {
        return generateTableName(ruleId, LocalDateTime.now());
    }
    
    /**
     * 生成建表SQL语句
     * @param tableName 表名
     * @return 建表SQL
     */
    public static String generateCreateTableSql(String tableName) {
        return "CREATE TABLE IF NOT EXISTS `" + tableName + "` (" +
                "`result_id` varchar(32) NOT NULL COMMENT '结果ID'," +
                "`rule_id` varchar(32) NOT NULL COMMENT '规则ID'," +
                "`group_key` text COMMENT '分组键'," +
                "`sum_value` double DEFAULT NULL COMMENT '求和值'," +
                "`avg_value` double DEFAULT NULL COMMENT '平均值'," +
                "`max_value` double DEFAULT NULL COMMENT '最大值'," +
                "`min_value` double DEFAULT NULL COMMENT '最小值'," +
                "`count_value` bigint DEFAULT NULL COMMENT '计数值'," +
                "`stat_time` datetime NOT NULL COMMENT '统计时间'," +
                "`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'," +
                "PRIMARY KEY (`result_id`)," +
                "KEY `idx_rule_id` (`rule_id`)," +
                "KEY `idx_stat_time` (`stat_time`)," +
                "KEY `idx_rule_stat_time` (`rule_id`,`stat_time`)" +
                ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='统计结果表';";
    }
    
    /**
     * 生成表是否存在SQL语句
     * @param tableName 表名
     * @return 查询SQL
     */
    public static String generateTableExistsSql(String tableName) {
        return "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = '" + tableName + "'";
    }
    
    /**
     * 生成获取最新统计时间SQL语句
     * @param tableName 表名
     * @param ruleId 规则ID
     * @return 查询SQL
     */
    public static String generateLatestStatTimeSql(String tableName, String ruleId) {
        return "SELECT MAX(stat_time) FROM `" + tableName + "` WHERE rule_id = '" + ruleId + "'";
    }
    
    /**
     * 生成批量插入SQL语句（带重复键更新）
     * @param tableName 表名
     * @return 插入SQL
     */
    public static String generateBatchInsertSql(String tableName) {
        return "INSERT INTO `" + tableName + "` (result_id, rule_id, group_key, sum_value, avg_value, max_value, min_value, count_value, stat_time, create_time) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW()) " +
                "ON DUPLICATE KEY UPDATE " +
                "sum_value = VALUES(sum_value), " +
                "avg_value = VALUES(avg_value), " +
                "max_value = VALUES(max_value), " +
                "min_value = VALUES(min_value), " +
                "count_value = VALUES(count_value), " +
                "stat_time = VALUES(stat_time), " +
                "create_time = NOW()";
    }
    
    /**
     * 生成按时间戳去重的查询SQL
     * @param tableName 表名
     * @param ruleId 规则ID
     * @param statTime 统计时间
     * @return 查询SQL
     */
    public static String generateExistsByTimeSql(String tableName, String ruleId, LocalDateTime statTime) {
        return "SELECT COUNT(*) FROM `" + tableName + "` WHERE rule_id = '" + ruleId + "' AND stat_time = '" + statTime + "'";
    }
    
    /**
     * 验证表名格式
     * @param tableName 表名
     * @return 是否有效
     */
    public static boolean isValidTableName(String tableName) {
        if (tableName == null || tableName.isEmpty()) {
            return false;
        }
        // 表名必须以 stat_result_ 开头，后面跟规则ID和日期
        return tableName.matches("^stat_result_[a-zA-Z0-9]+_[0-9]{8}$");
    }
    
    /**
     * 从表名中提取规则ID
     * @param tableName 表名
     * @return 规则ID
     */
    public static String extractRuleIdFromTableName(String tableName) {
        if (!isValidTableName(tableName)) {
            throw new IllegalArgumentException("无效的表名格式: " + tableName);
        }
        String[] parts = tableName.split("_");
        if (parts.length >= 4) {
            // stat_result_{ruleId}_{date} 格式
            return parts[2];
        }
        throw new IllegalArgumentException("无法从表名中提取规则ID: " + tableName);
    }
    
    /**
     * 从表名中提取日期
     * @param tableName 表名
     * @return 日期字符串（yyyyMMdd）
     */
    public static String extractDateFromTableName(String tableName) {
        if (!isValidTableName(tableName)) {
            throw new IllegalArgumentException("无效的表名格式: " + tableName);
        }
        String[] parts = tableName.split("_");
        if (parts.length >= 4) {
            return parts[3];
        }
        throw new IllegalArgumentException("无法从表名中提取日期: " + tableName);
    }
}